"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试
Case Name   : MOT不支持raw数据类型，类型强转后可插入数据
Create At   : 2022/05/17
Owner       : opentestcase027
Description :
    1.设置enable_incremental_checkpoint参数为off,并重启数据库
    2.创建自定义转化函数
    3.创建内存表，字段类型含integer
    4.创建自定义转化关系
    5.隐含调用raw类型转化为整型，插入数据
    6.清理环境，恢复配置
Expect      :
    1.设置成功，重启数据库成功
    2.自定义转化函数创建成功
    3.创建内存表成功
    4.自定义转化关系创建成功
    5.类型转化成功，数据插入成功
    6.清理环境及恢复配置成功
History     :
"""

import unittest
import os
import time
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
logger = Logger()


class TestCreateCast0076(unittest.TestCase):
    def setUp(self):
        logger.info(f'--------{os.path.basename(__file__)} start--------')
        self.sh_primysh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.function_name = 'f_create_cast_0076'
        self.mot_table = 't_create_cast_0076'
        self.config_item = "enable_incremental_checkpoint"
        text = '--------step1:修改enable_incremental_checkpoint为off  ' \
               'expect:修改成功，重启数据库成功--------'
        logger.info(text)
        check_res = self.sh_primysh.execut_db_sql(f'show {self.config_item}')
        logger.info(check_res)
        self.assertTrue(len(check_res.splitlines()) == 4, '执行失败'+text)
        self.init_config = check_res.splitlines()[2].strip()
        self.assertTrue("on" in self.init_config or
                        "off" in self.init_config, '执行失败:' + text)
        if 'on' in self.init_config:
            self.sh_primysh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'{self.config_item} = off')
            self.sh_primysh.restart_db_cluster()
            time.sleep(3)
            status = self.sh_primysh.get_db_cluster_status()
            logger.info(status)
            self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)

    def test_create_cast_0076(self):
        text = '--------step2:创建自定义转化函数   expect:函数创建成功--------'
        logger.info(text)
        self.func_cmd = f'''
        create or replace function {self.function_name}(in raw)
               returns integer
               as
               \$\$
               declare
               begin
               return 10;
               end;
               \$\$
               language plpgsql;                       
                      '''
        logger.info(self.func_cmd)
        function_msg = self.sh_primysh.execut_db_sql(self.func_cmd)
        logger.info(function_msg)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                      function_msg, '执行失败' + text)
        text = '--------step3:创建内存表，字段类型含integer ' \
               'expect：创建成功--------'
        logger.info(text)
        self.sql_cmd = f'''
        drop table if exists {self.mot_table} cascade;
        drop foreign table if exists {self.mot_table} cascade;
        create foreign table {self.mot_table}
        (i integer, b time with time zone);'''
        logger.info(self.sql_cmd)
        sql_msg = self.sh_primysh.execut_db_sql(self.sql_cmd)
        logger.info(sql_msg)
        self.assertIn(self.constant.CREATE_FOREIGN_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        text = '--------step4: 创建自定义转换关系    expect: 创建成功--------'
        logger.info(text)
        self.cast_cmd = f'''
        drop cast if exists (raw as integer) cascade;
        drop cast if exists (text as time with time zone ) cascade;
        create cast(raw as integer) with function {self.function_name}
        (raw) as implicit;
        create cast(text as time with time zone) with inout as implicit;
        '''
        logger.info(self.cast_cmd)
        cast_msg = self.sh_primysh.execut_db_sql(self.cast_cmd)
        logger.info(cast_msg)
        self.assertIn('CREATE CAST', cast_msg, '执行失败' + text)
        text = '--------step5:隐含调用raw强转为整型的数据类型插入内存表中   ' \
               'expect: 插入数据成功--------'
        logger.info(text)
        self.insert_cmd = f'''
        insert into {self.mot_table} values(hextoraw('A'), '205609');
        select * from {self.mot_table};'''
        logger.info(self.insert_cmd)
        insert_msg = self.sh_primysh.execut_db_sql(self.insert_cmd)
        logger.info(insert_msg)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      insert_msg, '执行失败' + text)

    def tearDown(self):
        text = '--------step6:后置处理,清理环境   expect:清理成功--------'
        logger.info(text)
        self.rm_cmd = f'''
        drop cast if exists (raw as integer) cascade;
        drop function if exists {self.function_name}(raw) cascade;
        drop foreign table if exists {self.mot_table} cascade;
        '''
        logger.info(self.rm_cmd)
        rm_msg = self.sh_primysh.execut_db_sql(self.rm_cmd)
        logger.info(rm_msg)
        logger.info('--------恢复enable_incremental_checkpoint默认配置--------')
        self.assertTrue("on" in self.init_config or "off" in self.init_config)
        if "on" in self.init_config:
            self.mod_msg = self.sh_primysh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f'{self.config_item} = on')
            logger.info('--------重启数据库--------')
            restart_msg = self.sh_primysh.restart_db_cluster()
            time.sleep(3)
            logger.info(restart_msg)
            status = self.sh_primysh.get_db_cluster_status()
            logger.info(status)
            self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)
        else:
            logger.info('--------参数enable_incremental_checkpoint未做修改,'
                        '不用恢复--------')

        self.assertIn(self.constant.DROP_FOREIGN_SUCCESS_MSG, 
                      rm_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, 
                      rm_msg, '执行失败' + text)
        self.assertIn('DROP CAST', rm_msg, '执行失败' + text)
        logger.info(f'--------{os.path.basename(__file__)} end--------')
